/* 
   TIME-STAMP: <05/08/09 14:03:30 OSTOLOP>
   SQL FILE FOR CREATING THE DATABASE IN ORACLE VIA DDL
*/

/* SCRIPT TO DROP & CREATE EVERYTHING: */

DROP TABLE EP_ACTION_METADATA CASCADE CONSTRAINTS;
DROP TABLE EP_COMPONENT CASCADE CONSTRAINTS;
DROP TABLE EP_DATASET CASCADE CONSTRAINTS;
DROP TABLE EP_DATASET_METADATA CASCADE CONSTRAINTS;
DROP TABLE EP_DATASET_METADATA_TYPE CASCADE CONSTRAINTS;
DROP TABLE EP_DATASET_METADATA_VALUE CASCADE CONSTRAINTS;
DROP TABLE EP_GROUP CASCADE CONSTRAINTS;
DROP TABLE EP_USER CASCADE CONSTRAINTS;
DROP TABLE EP_ERROR CASCADE CONSTRAINTS;
DROP TABLE EP_FOLDER CASCADE CONSTRAINTS;
DROP TABLE EP_NOTES CASCADE CONSTRAINTS;
DROP TABLE EP_USER_GROUP CASCADE CONSTRAINTS;
DROP TABLE EP_ACTION CASCADE CONSTRAINTS;
DROP TABLE EP_SESSION CASCADE CONSTRAINTS;

DROP SEQUENCE EP_ACTION_METADATA_SEQ;
DROP SEQUENCE EP_ACTION_SEQ;
DROP SEQUENCE EP_DATASET_METADATA_SEQ;
DROP SEQUENCE EP_DATASET_METADATA_TYPE_SEQ;
DROP SEQUENCE EP_DATASET_METADATA_VALUE_SEQ;
DROP SEQUENCE EP_DATASET_SEQ;
DROP SEQUENCE EP_GROUP_SEQ;
DROP SEQUENCE EP_USER_SEQ;
DROP SEQUENCE EP_FOLDER_SEQ;
DROP SEQUENCE EP_NOTES_SEQ;

DROP VIEW EP_ACTION_VIEW;
DROP VIEW EP_DATASET_VIEW;
DROP VIEW EP_EXT_ACTION_VIEW;

CREATE SEQUENCE EP_USER_SEQ;
CREATE SEQUENCE EP_GROUP_SEQ;
CREATE SEQUENCE EP_FOLDER_SEQ;
CREATE SEQUENCE EP_DATASET_SEQ;
CREATE SEQUENCE EP_ACTION_SEQ;
CREATE SEQUENCE EP_DATASET_METADATA_SEQ;
CREATE SEQUENCE EP_DATASET_METADATA_TYPE_SEQ;
CREATE SEQUENCE EP_DATASET_METADATA_VALUE_SEQ;
CREATE SEQUENCE EP_ACTION_METADATA_SEQ;
CREATE SEQUENCE EP_NOTES_SEQ;

CREATE TABLE EP_COMPONENT (
    ID INTEGER NOT NULL PRIMARY KEY,
    NAME CHARACTER VARYING(100),
    XML CHARACTER VARYING(100) NOT NULL
);

CREATE TABLE EP_ERROR (
    ID INTEGER NOT NULL PRIMARY KEY,
    MESSAGE CHARACTER VARYING(255),
    SEVERITY SMALLINT DEFAULT 0 NOT NULL
);

CREATE TABLE EP_USER (
    ID INTEGER NOT NULL PRIMARY KEY,
    USERNAME CHARACTER VARYING(40) NOT NULL UNIQUE,
    PASSWORD CHARACTER VARYING(40) NOT NULL,
    SALT CHARACTER(2) NOT NULL,
    SESSION_ONLY SMALLINT DEFAULT 0 NOT NULL,
    FIRST_NAME CHARACTER VARYING(40) NOT NULL,
    LAST_NAME CHARACTER VARYING(40) NOT NULL,
    INSTITUTE CHARACTER VARYING(255),
    DEPARTMENT CHARACTER VARYING(255),
    ADDRESS CHARACTER VARYING(255),
    TELEPHONE CHARACTER VARYING(255),
    EMAIL CHARACTER VARYING(100) NOT NULL,
    LAST_FOLDER INTEGER,
    LAST_ACTION INTEGER,
    LAST_ROOT_DATASET INTEGER,
    LAST_CLUSTER_SELECTION CHARACTER VARYING(255),
    USER_LEVEL INTEGER DEFAULT 0,
    VISUALIZATION_FORMAT INTEGER DEFAULT 0,
    SHOW_ANALYSIS_HISTORY INTEGER DEFAULT 1,
    SHOW_CONTEXT_HELP INTEGER DEFAULT 1,
    EPC_SIZE_STATE INTEGER DEFAULT 1,
    CREATED CHARACTER VARYING(40) DEFAULT SYSTIMESTAMP NOT NULL,
    LAST_LOGIN CHARACTER VARYING(40) DEFAULT SYSTIMESTAMP NOT NULL
);

CREATE TRIGGER T_EP_USER_ID BEFORE INSERT ON EP_USER
FOR EACH ROW
WHEN (NEW.ID IS NULL)
BEGIN
 SELECT EP_USER_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/

CREATE TABLE EP_FOLDER (
    ID INTEGER NOT NULL PRIMARY KEY,
    NAME CHARACTER VARYING(40) NOT NULL,
    PARENT INTEGER REFERENCES EP_FOLDER ON DELETE CASCADE,
    LOCATION CHARACTER VARYING(255) UNIQUE,
    OWNER INTEGER REFERENCES EP_USER ON DELETE CASCADE,
    GROUP_WRITE SMALLINT NOT NULL,
    GROUP_READ SMALLINT NOT NULL,
    OTHER_READ SMALLINT NOT NULL
);

CREATE TRIGGER T_FOLDER_ID BEFORE INSERT ON EP_FOLDER
FOR EACH ROW
WHEN (NEW.ID IS NULL)
BEGIN
 SELECT EP_FOLDER_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/

CREATE TABLE EP_SESSION (
    ID CHARACTER(32) NOT NULL PRIMARY KEY,
    OWNER INTEGER REFERENCES EP_USER ON DELETE CASCADE,
    CREATE_DATE CHARACTER VARYING(100),
    EXPIRE CHARACTER VARYING(100),
    QUERY_ID CHARACTER VARYING(50),
    STATUS CHARACTER VARYING(100),
    CURRENT_FOLDER INTEGER,
    CURRENT_ACTION INTEGER,
    CURRENT_ROOT_DATASET INTEGER,
    CURRENT_CLUSTER_SELECTION CHARACTER VARYING(255),
    CURRENT_METADATA_SELECTION INTEGER
);

CREATE TABLE EP_GROUP (
    ID INTEGER NOT NULL PRIMARY KEY,
    NAME CHARACTER VARYING(40) NOT NULL,
    DESCRIPTION CHARACTER VARYING(255),
    ADMIN INTEGER NOT NULL REFERENCES EP_USER
);

CREATE TABLE EP_USER_GROUP (
    EP_USER INTEGER NOT NULL REFERENCES EP_USER,
    EP_GROUP INTEGER NOT NULL REFERENCES EP_GROUP,
    PRIMARY KEY (EP_USER, EP_GROUP)
);

CREATE TABLE EP_DATASET (
    ID INTEGER NOT NULL PRIMARY KEY,
    NAME CHARACTER VARYING(100),
    DESCRIPTION CHARACTER VARYING(255),
    TYPE CHARACTER VARYING(100) NOT NULL,
    SPECIES CHARACTER VARYING(100) DEFAULT 'UNKNOWN' NOT NULL,
    FILENAME CHARACTER VARYING(255) NOT NULL,
    FOLDER INTEGER NOT NULL REFERENCES EP_FOLDER ON DELETE CASCADE,
    EP_SESSION CHARACTER(32) REFERENCES EP_SESSION ON DELETE CASCADE,
    OWNER INTEGER NOT NULL REFERENCES EP_USER ON DELETE CASCADE,
    GROUP_WRITE SMALLINT NOT NULL,
    GROUP_READ SMALLINT NOT NULL,
    OTHER_READ SMALLINT NOT NULL,
    CREATED TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
    LAST_USED TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
);

CREATE TRIGGER T_DATASET_ID BEFORE INSERT ON EP_DATASET
FOR EACH ROW
WHEN (NEW.ID IS NULL)
BEGIN
 SELECT EP_DATASET_SEQ.NEXTVAL, SYSTIMESTAMP INTO :NEW.ID, :NEW.CREATED FROM DUAL;
END;
/

CREATE TABLE EP_DATASET_METADATA_TYPE (
    ID INTEGER NOT NULL PRIMARY KEY,
    NAME CHARACTER VARYING(30) NOT NULL,
    DESCRIPTION CHARACTER VARYING(500)
);

CREATE TRIGGER T_DATASET_METADATA_TYPE_ID BEFORE INSERT ON EP_DATASET_METADATA_TYPE
FOR EACH ROW
WHEN (NEW.ID IS NULL)
BEGIN
 SELECT EP_DATASET_METADATA_TYPE_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/

CREATE TABLE EP_DATASET_METADATA (
    ID INTEGER NOT NULL PRIMARY KEY,
    DATASET INTEGER NOT NULL REFERENCES EP_DATASET ON DELETE CASCADE,
    TYPE INTEGER NOT NULL REFERENCES EP_DATASET_METADATA_TYPE ON DELETE CASCADE
);

CREATE TRIGGER T_DATASET_METADATA_ID BEFORE INSERT ON EP_DATASET_METADATA
FOR EACH ROW
WHEN (NEW.ID IS NULL)
BEGIN
 SELECT EP_DATASET_METADATA_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/

CREATE TABLE EP_DATASET_METADATA_VALUE (
    ID INTEGER NOT NULL PRIMARY KEY,
    DATASET_METADATA INTEGER NOT NULL REFERENCES EP_DATASET_METADATA ON DELETE CASCADE,
    VALUE CHARACTER VARYING(255)
);

CREATE TRIGGER T_DATASET_METADATA_VALUE_ID BEFORE INSERT ON EP_DATASET_METADATA_VALUE
FOR EACH ROW
WHEN (NEW.ID IS NULL)
BEGIN
 SELECT EP_DATASET_METADATA_VALUE_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/

CREATE TABLE EP_ACTION (
    ID INTEGER NOT NULL PRIMARY KEY,
    PARENT_ACTION INTEGER REFERENCES EP_ACTION ON DELETE CASCADE,
    EP_DATE TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
    COMPONENT INTEGER NOT NULL REFERENCES EP_COMPONENT,
    DST_DATASET INTEGER NOT NULL REFERENCES EP_DATASET ON DELETE CASCADE,
    ROOT_DATASET INTEGER NOT NULL REFERENCES EP_DATASET ON DELETE CASCADE,
    METADATA CHARACTER VARYING(2000)
);

CREATE TRIGGER T_ACTION_ID BEFORE INSERT ON EP_ACTION
FOR EACH ROW
WHEN (NEW.ID IS NULL)
BEGIN
 SELECT EP_ACTION_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/

CREATE TABLE EP_ACTION_METADATA (
    ID INTEGER NOT NULL PRIMARY KEY,
    ACTION INTEGER NOT NULL REFERENCES EP_ACTION ON DELETE CASCADE,
    TYPE CHARACTER VARYING(30),
    UUID CHARACTER(36),
    VALUE CHARACTER VARYING(2000),
    LABEL CHARACTER VARYING(255),
    NAME CHARACTER VARYING(50) NOT NULL
);

CREATE TRIGGER T_ACTION_METADATA_ID BEFORE INSERT ON EP_ACTION_METADATA
FOR EACH ROW
WHEN (NEW.ID IS NULL)
BEGIN
 SELECT EP_ACTION_METADATA_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/

CREATE TABLE EP_NOTES (
    ID INTEGER NOT NULL PRIMARY KEY,
    META_ID INTEGER NOT NULL REFERENCES EP_ACTION ON DELETE CASCADE,
    VALUE CHARACTER VARYING (2000)
);

CREATE TRIGGER T_NOTES_ID BEFORE INSERT ON EP_NOTES
FOR EACH ROW
WHEN (NEW.ID IS NULL)
BEGIN
 SELECT EP_NOTES_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/


CREATE VIEW EP_DATASET_VIEW AS
    SELECT DATASET.ID, DATASET.NAME, DATASET.DESCRIPTION, DATASET.TYPE, DATASET.SPECIES, DATASET.EP_SESSION, FOLDER.ID AS FOLDER_ID, FOLDER.LOCATION AS FOLDER_LOCATION, DATASET.FILENAME, DATASET.OWNER AS EP_USER_ID, DATASET.GROUP_WRITE, DATASET.GROUP_READ, DATASET.OTHER_READ, DATASET.CREATED, DATASET.LAST_USED FROM EP_DATASET DATASET, EP_FOLDER FOLDER WHERE (DATASET.FOLDER = FOLDER.ID);

CREATE VIEW EP_ACTION_VIEW AS
    SELECT EP_ACTION.ID, EP_ACTION.PARENT_ACTION, EP_ACTION.ROOT_DATASET, EP_ACTION.DST_DATASET, EP_ACTION.COMPONENT, COMPONENT.NAME AS COMPONENT_NAME, EP_ACTION.EP_DATE, EP_ACTION.METADATA AS ACTION_METADATA FROM (EP_ACTION JOIN EP_COMPONENT COMPONENT ON ((COMPONENT.ID = EP_ACTION.COMPONENT))) ORDER BY EP_ACTION.EP_DATE;

CREATE VIEW EP_EXT_ACTION_VIEW AS
    SELECT EP_ACTION.ID AS ACTION_ID, EP_ACTION.PARENT_ACTION AS PARENT_ACTION_ID, EP_ACTION.ROOT_DATASET AS ROOT_DATASET_ID, EP_ACTION.DST_DATASET AS DST_DATASET_ID, DATASET.SPECIES AS DST_DATASET_SPECIES, DATASET.TYPE AS DST_DATASET_TYPE, DATASET.FILENAME AS DST_DATASET_FILENAME, FOLDER.LOCATION AS DST_DATASET_LOCATION, EP_ACTION.COMPONENT AS EPC_ID, COMPONENT.NAME AS EPC_NAME, TO_CHAR ( EP_ACTION.EP_DATE, 'Dy Mon dd HH24:MI:SS YYYY' ) AS ACTION_DATE, DATASET_METADATA_TYPE.NAME AS DATASET_METADATA_TYPE, DATASET_METADATA_VALUE.VALUE AS DATASET_METADATA_VALUE, ACTION_METADATA.ID AS ACTION_METADATA_ID, ACTION_METADATA.TYPE AS ACTION_METADATA_TYPE, ACTION_METADATA.LABEL AS ACTION_METADATA_LABEL, ACTION_METADATA.VALUE AS ACTION_METADATA_VALUE, ACTION_METADATA.UUID AS ACTION_METADATA_UUID, ACTION_METADATA.NAME AS ACTION_METADATA_NAME FROM (((((((EP_ACTION LEFT JOIN EP_COMPONENT COMPONENT ON ((COMPONENT.ID = EP_ACTION.COMPONENT))) LEFT JOIN EP_ACTION_METADATA ACTION_METADATA ON ((ACTION_METADATA.ACTION = EP_ACTION.ID))) LEFT JOIN EP_DATASET_METADATA DATASET_METADATA ON ((DATASET_METADATA.DATASET = EP_ACTION.DST_DATASET))) LEFT JOIN EP_DATASET_METADATA_VALUE DATASET_METADATA_VALUE ON ((DATASET_METADATA.ID = DATASET_METADATA_VALUE.DATASET_METADATA))) LEFT JOIN EP_DATASET_METADATA_TYPE DATASET_METADATA_TYPE ON ((DATASET_METADATA.TYPE = DATASET_METADATA_TYPE.ID))) LEFT JOIN EP_DATASET DATASET ON ((DATASET.ID = EP_ACTION.DST_DATASET))) LEFT JOIN EP_FOLDER FOLDER ON ((FOLDER.ID = DATASET.FOLDER))) ORDER BY EP_ACTION.EP_DATE;
